package zy.vo.stock;

import java.util.HashMap;
import java.util.Map;

import zy.util.CommonUtil;
import zy.util.StringUtil;

public class SingleTrackSQL {
	public static String getSingleTrackSQL(Map<String, Object> params){
		Map<String,String> mapSqls=new HashMap<String, String>();
		mapSqls.put("Init", getInit(params));
		mapSqls.put("BuyEnter", getBuyEnter(params));
		mapSqls.put("BatchSell", getBatchSell(params));
		mapSqls.put("SellShop", getSellShop(params));
		mapSqls.put("SellShopHH", getSellShopHH(params));
		mapSqls.put("AllotSend", getAllotSend(params));
		mapSqls.put("AllotReceive", getAllotReceive(params));
		mapSqls.put("AllocateIn", getAllocateIn(params));
		mapSqls.put("AllocateOut", getAllocateOut(params));
		mapSqls.put("Adjust", getAdjust(params));
		mapSqls.put("Loss", getLoss(params));
		mapSqls.put("OverFlow", getOverFlow(params));
		mapSqls.put("WantSend", getWantSend(params));
		mapSqls.put("WantReceive", getWantReceive(params));
		mapSqls.put("SellAllocateIn", getSellAllocateIn(params));
		mapSqls.put("SellAllocateOut", getSellAllocateOut(params));
		StringBuffer sql=new StringBuffer();
		for (String key : mapSqls.keySet()) {
		    String value = mapSqls.get(key);
		    sql.append(value+" UNION ALL ");
		}
		sql.delete(sql.length()-10,sql.length());
		StringBuffer unionSql = new StringBuffer();
		
		unionSql.append(" SELECT dp_name,dp_code,sub_code,DATE_FORMAT(dl_date,'%Y-%m-%d') AS dl_date,dl_type,dl_number,pd_code,pd_no,pd_name,cr_code,sz_code,br_code,pd_bd_code,pd_tp_code,dl_in_amount,dl_out_amount,");
		unionSql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = temp.cr_code AND cr.companyid = :companyid LIMIT 1) AS cr_name,");
		unionSql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = temp.sz_code AND sz.companyid = :companyid LIMIT 1) AS sz_name,");
		unionSql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = temp.br_code AND br.companyid = :companyid LIMIT 1) AS br_name,");
		unionSql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = :companyid LIMIT 1) AS bd_name,");
		unionSql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = :companyid LIMIT 1) AS tp_name");
		unionSql.append(" FROM (");
		sql.insert(0, unionSql);
		sql.append(") temp");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY dl_date DESC");
		}
		sql.append(" LIMIT :start,:end");
		return sql.toString();
	}
	
	public static String getSingleTrackCountSumSQL(Map<String, Object> params){
		Map<String,String> mapSqls=new HashMap<String, String>();
		mapSqls.put("Init", getInit(params));
		mapSqls.put("BuyEnter", getBuyEnter(params));
		mapSqls.put("BatchSell", getBatchSell(params));
		mapSqls.put("SellShop", getSellShop(params));
		mapSqls.put("SellShopHH", getSellShopHH(params));
		mapSqls.put("AllotSend", getAllotSend(params));
		mapSqls.put("AllotReceive", getAllotReceive(params));
		mapSqls.put("AllocateIn", getAllocateIn(params));
		mapSqls.put("AllocateOut", getAllocateOut(params));
		mapSqls.put("Adjust", getAdjust(params));
		mapSqls.put("Loss", getLoss(params));
		mapSqls.put("OverFlow", getOverFlow(params));
		mapSqls.put("WantSend", getWantSend(params));
		mapSqls.put("WantReceive", getWantReceive(params));
		mapSqls.put("SellAllocateIn", getSellAllocateIn(params));
		mapSqls.put("SellAllocateOut", getSellAllocateOut(params));
		StringBuffer sql=new StringBuffer();
		for (String key : mapSqls.keySet()) {
		    String value = mapSqls.get(key);
		    sql.append(value+" UNION ALL ");
		}
		sql.delete(sql.length()-10,sql.length());
		sql.insert(0, "SELECT COUNT(1) AS totalCount,SUM(dl_in_amount) dl_in_amount,SUM(dl_out_amount) dl_out_amount FROM (");
		sql.append(") temp");
		return sql.toString();
	}
	
	private static String getInit(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,sd_code AS sub_code,sd_date AS dl_date,'期初' AS dl_type,'' AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,sd_cr_code AS cr_code,sd_sz_code AS sz_code,sd_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" sd_init AS dl_in_amount,0 AS dl_out_amount");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND sd_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND sd_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND sd_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND sd_date <= :enddate ");
		}
		sql.append(" AND sd_init <> 0");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getBuyEnter(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,etl_sub_code AS sub_code,et_ar_date AS dl_date,IF(et_type = 0,'进货入库','进货出库') AS dl_type,et_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,etl_cr_code AS cr_code,etl_sz_code AS sz_code,etl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" IF(et_type=0,etl_amount,0) AS dl_in_amount,IF(et_type=1,ABS(etl_amount),0) AS dl_out_amount");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = et_depot_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND etl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND etl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND et_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND et_ar_date <= :enddate ");
		}
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getBatchSell(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,sel_sub_code AS sub_code,se_ar_date AS dl_date,IF(se_type = 0,'批发出库','批发入库') AS dl_type,se_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,sel_cr_code AS cr_code,sel_sz_code AS sz_code,sel_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" IF(se_type=1,ABS(sel_amount),0) AS dl_in_amount,IF(se_type=0,ABS(sel_amount),0) AS dl_out_amount");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = se_depot_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND sel_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND sel_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND se_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND se_ar_date <= :enddate ");
		}
		sql.append(" AND se_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getSellShop(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,shl_sub_code AS sub_code,shl_date AS dl_date,IF(sh_state = 0,'零售出库','零售入库') AS dl_type,sh_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,shl_cr_code AS cr_code,shl_sz_code AS sz_code,shl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" IF(sh_state=1,ABS(shl_amount),0) AS dl_in_amount,IF(sh_state=0,ABS(shl_amount),0) AS dl_out_amount");
		sql.append(" FROM t_sell_shop t");
		sql.append(" JOIN t_sell_shoplist shl ON shl_number = sh_number AND shl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = shl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = shl_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND shl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND shl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND shl_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND shl_date <= :enddate ");
		}
		sql.append(" AND shl_state IN(0,1)");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getSellShopHH(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,shl_sub_code AS sub_code,shl_date AS dl_date,IF(shl_amount < 0,'换货入库','换货出库') AS dl_type,sh_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,shl_cr_code AS cr_code,shl_sz_code AS sz_code,shl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" IF(shl_amount < 0,ABS(shl_amount),0) AS dl_in_amount,IF(shl_amount > 0,ABS(shl_amount),0) AS dl_out_amount");
		sql.append(" FROM t_sell_shop t");
		sql.append(" JOIN t_sell_shoplist shl ON shl_number = sh_number AND shl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = shl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = shl_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND shl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND shl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND shl_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND shl_date <= :enddate ");
		}
		sql.append(" AND shl_state = 2");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getAllotSend(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,atl_sub_code AS sub_code,at_date AS dl_date,IF(at_type = 0,'配货出库','配货退货出库') AS dl_type,at_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,atl_cr_code AS cr_code,atl_sz_code AS sz_code,atl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" 0 AS dl_in_amount,ABS(atl_sendamount) AS dl_out_amount");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_sort_allotlist atl ON atl_number = at_number AND atl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = atl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = at_outdp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND atl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND atl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND at_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND at_date <= :enddate ");
		}
		sql.append(" AND at_ar_state IN(3,4,5)");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getAllotReceive(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,atl_sub_code AS sub_code,at_date AS dl_date,IF(at_type = 0,'配货入库','配货退货入库') AS dl_type,at_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,atl_cr_code AS cr_code,atl_sz_code AS sz_code,atl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" ABS(atl_sendamount) AS dl_in_amount,0 AS dl_out_amount");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_sort_allotlist atl ON atl_number = at_number AND atl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = atl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = at_indp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND atl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND atl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND at_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND at_date <= :enddate ");
		}
		sql.append(" AND at_ar_state = 4");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getAllocateIn(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,acl_sub_code AS sub_code,ac_ar_date AS dl_date,'调拨入库' AS dl_type,ac_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,acl_cr_code AS cr_code,acl_sz_code AS sz_code,acl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" ABS(acl_amount) AS dl_in_amount,0 AS dl_out_amount");
		sql.append(" FROM t_stock_allocate t");
		sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_indp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND acl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND acl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND ac_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND ac_ar_date <= :enddate ");
		}
		sql.append(" AND ac_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getAllocateOut(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,acl_sub_code AS sub_code,ac_ar_date AS dl_date,'调拨出库' AS dl_type,ac_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,acl_cr_code AS cr_code,acl_sz_code AS sz_code,acl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" 0 AS dl_in_amount,ABS(acl_amount) AS dl_out_amount");
		sql.append(" FROM t_stock_allocate t");
		sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_outdp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND acl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND acl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND ac_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND ac_ar_date <= :enddate ");
		}
		sql.append(" AND ac_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getAdjust(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,ajl_sub_code AS sub_code,aj_ar_date AS dl_date,IF(ajl_amount>0,'调整入库','调整出库') AS dl_type,aj_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,ajl_cr_code AS cr_code,ajl_sz_code AS sz_code,ajl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" IF(ajl_amount>0,ABS(ajl_amount),0) AS dl_in_amount,IF(ajl_amount<0,ABS(ajl_amount),0) AS dl_out_amount");
		sql.append(" FROM t_stock_adjust t");
		sql.append(" JOIN t_stock_adjustlist ajl ON ajl_number = aj_number AND ajl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = ajl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = aj_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND ajl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND ajl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND aj_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND aj_ar_date <= :enddate ");
		}
		sql.append(" AND aj_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getLoss(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,lol_sub_code AS sub_code,lo_ar_date AS dl_date,'报损' AS dl_type,lo_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,lol_cr_code AS cr_code,lol_sz_code AS sz_code,lol_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" 0 AS dl_in_amount,ABS(lol_amount) AS dl_out_amount");
		sql.append(" FROM t_stock_loss t");
		sql.append(" JOIN t_stock_losslist lol ON lol_number = lo_number AND lol.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = lol_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = lo_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND lol_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND lol_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND lo_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND lo_ar_date <= :enddate ");
		}
		sql.append(" AND lo_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getOverFlow(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,ofl_sub_code AS sub_code,of_ar_date AS dl_date,'报溢' AS dl_type,of_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,ofl_cr_code AS cr_code,ofl_sz_code AS sz_code,ofl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" ABS(ofl_amount) AS dl_in_amount,0 AS dl_out_amount");
		sql.append(" FROM t_stock_overflow t");
		sql.append(" JOIN t_stock_overflowlist ofl ON ofl_number = of_number AND ofl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = ofl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = of_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND ofl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND ofl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND of_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND of_ar_date <= :enddate ");
		}
		sql.append(" AND of_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getWantSend(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,wtl_sub_code AS sub_code,wt_date AS dl_date,IF(wt_type = 0,'补货出库','退货出库') AS dl_type,wt_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,wtl_cr_code AS cr_code,wtl_sz_code AS sz_code,wtl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" 0 AS dl_in_amount,ABS(wtl_sendamount) AS dl_out_amount");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = wtl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = wt_outdp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND wtl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND wtl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND wt_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND wt_date <= :enddate ");
		}
		sql.append(" AND wt_ar_state IN(3,4,5)");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getWantReceive(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,wtl_sub_code  AS sub_code,wt_date AS dl_date,IF(wt_type = 0,'补货入库','退货入库') AS dl_type,wt_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,wtl_cr_code AS cr_code,wtl_sz_code AS sz_code,wtl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" ABS(wtl_sendamount) AS dl_in_amount,0 AS dl_out_amount");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = wtl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = wt_indp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND wtl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND wtl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND wt_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND wt_date <= :enddate ");
		}
		sql.append(" AND wt_ar_state = 4");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getSellAllocateOut(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,acl_sub_code AS sub_code,ac_date AS dl_date,'前台调拨出库' AS dl_type,ac_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,acl_cr_code AS cr_code,acl_sz_code AS sz_code,acl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" 0 AS dl_in_amount,ABS(acl_amount) AS dl_out_amount");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" JOIN t_sell_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_out_dp AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND acl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND acl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND ac_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND ac_date <= :enddate ");
		}
		sql.append(" AND ac_state IN(1,2,3)");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	private static String getSellAllocateIn(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,dp_code,acl_sub_code AS sub_code,ac_date AS dl_date,'前台调拨入库' AS dl_type,ac_number AS dl_number,");
		sql.append(" pd_code,pd_no,pd_name,acl_cr_code AS cr_code,acl_sz_code AS sz_code,acl_br_code AS br_code,pd_bd_code,pd_tp_code,");
		sql.append(" ABS(acl_amount) AS dl_in_amount,0 AS dl_out_amount");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" JOIN t_sell_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_in_dp AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code");
		}else {
			sql.append(" AND dp_code IN (:allDepot)");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND acl_cr_code = :cr_code");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND acl_sz_code = :sz_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND ac_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND ac_date <= :enddate ");
		}
		sql.append(" AND ac_state =2");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
}
